import re
import shutil
import sqlite3
import os
from config import ConfigManager
from log import AppLogger
import time 
import gv
def parse_scp(spc_fullname,cpfile=True):
    '''
    解析spc.csv文件
    return: file_info,c
    '''
    log =  AppLogger('my_app.log', max_bytes=1024*5000, backup_count=2)
    config = ConfigManager("config.ini")
    paths = config.paths()
    # 获取文件名各段信息
    # spc_fullname  SPC@NZFM08-X3A-011-12@182323027903050000000206@6@20240112112320.csv 
    # spc_fullname 是  fullname，获取其中的 path  和 filename(不包括文件扩展名)
    spc_fullname = os.path.normpath(spc_fullname)
    
    spc_file = os.path.basename(spc_fullname)
    path = os.path.dirname(spc_fullname)
    
    file_info = spc_file.split('.')[0].split('@')
    file_info = {
        'ProgramName':file_info[1],
        'BoardID':file_info[2],
        'LotNo':file_info[3],
        'SpcTime':file_info[4]
    }
    
    # 把 file_info 转成 json字符串
    log.info("分析文件名结束：" + str(file_info))
    
    #把文件里的第2行的值名作为字典的key，第3行的值名作为字典的value
    # 打开文件
    scp=''
    for i in range(0,5):
        try:
            scp = open(spc_fullname,'r',encoding='utf8') 
            break
        except PermissionError as ex:
            time.sleep(0.5)
    else:
        raise Exception('文件打开失败:'+spc_fullname)
    
    scp.readline()
    log.info("读取第一行成功" )
    
    keys = scp.readline().strip().split(',')
    # 移除 keys 里的空值，保持顺序
    keys = [key for key in keys if key.strip()]
    vals = scp.readline().strip().split(',')[0:len(keys)]
    scp_dict = dict(zip(keys,vals))
    
    log.info("分析文件头结束" )
    
    # 定义一个字符串，只包括 windows合法文件名符号以外的字符
    illegal_str = r'[\\/:*?"<>|+]'
    
    # scp_dict['BoardID'] 判断BoardID是否 包括 illegal_str这里的字符，是就抛出异常
    if re.search(illegal_str,scp_dict['BoardID']):
        log.error('BoardID包含非法字符,BoardID is illegal')
        gv.copyfile(spc_fullname,paths['epath']+'/'+spc_file)
        raise Exception('BoardID包含非法字符,BoardID is illegal')
    
    log.info("BoardID 合法" )
    
    # 打开一个sqlite内存库，以keys为各列名，建立 名为spc的表
    conn = sqlite3.connect(':memory:')
    # conn = sqlite3.connect(spc_file+".sqlite")
    # 设置为自动提交模式
    conn.isolation_level = None
    c = conn.cursor()
    try:
        c.execute('create table head (%s)' % ','.join(keys))
        c.execute('INSERT INTO head VALUES (%s)' % ','.join(['?'] * len(vals)), vals)
    except Exception as ex:
        log.error("处理head 出错：" + str(ex))
        raise Exception("处理head 出错：" + str(ex))
    
    log.info("写入head信息完" )
    
    log.info("分析文件2 3 行结束：keys:%s,vals:%s"%(len(keys),len(vals)))
    
    line=''
    for line in scp:
        if line.startswith('RefNo'):
            break
    else:
        raise Exception('No RefNo')
    
    spc_keys = line.strip().split(',')
    spc_keys = [key for key in spc_keys if key.strip()]
    # 以spc_keys为各列名，建立 名为spc的表
    try:
        sql = 'create table spc (%s)' % ','.join(spc_keys)
        log.info("分析文件spc 列结束： spc_keys:%s"%(len(spc_keys)))
        c.execute(sql)
        
        for line in scp:
            values = line.strip().split(',')
            # 如果 AutoResult 为3 则忽略这一行
            if values[16]=='3':
                continue
            # 如果values[17]为1"gn" 则 修改values[32] 为新格式
            if values[17] == '1' and values[32]:
                oldFile = values[32]
                ext = values[32].split('.')[-1]
                # 获取 values[32] 这个fullname的路径
                path =  os.path.dirname(values[32])

                #程序名/ProgramName	条码/BoardID	NG理由/NgReason[14] 	元件名+原件库名/PartsName+LibPartsName values[5]	拼版号/BlockNO
                # values[32] = scp_dict['ProgramName'] +"-"+ scp_dict['BoardID'] +"-"+ values[14] + "-" + values[5] + values[6] + values[1] 
                values[32] = scp_dict['ProgramName'] +"-"+ scp_dict['BoardID'] +"-"+ values[14] + "-" + values[5] + values[6] + "-" + values[1]+"."+ext 

                newFile = values[32]
                src = os.path.normpath(os.path.join(scp_dict['ImagePath'],oldFile))
                dst = os.path.normpath(os.path.join(paths['ppath'],newFile))
                values[32] = dst
                try:
                    if cpfile:
                        log.info("复制图片开始:")
                        log.info("复制源图片:%s"%(src))
                        log.info("复制到目标:%s"%(dst))
                        shutil.copyfile(src, dst)
                except Exception as ex:
                    log.error("复制文件出错:%s:%s,%s"%(str(ex),src,dst))

            sql = 'INSERT INTO spc VALUES (%s)' % ','.join(['?'] * len(values))
            c.execute(sql, values)
            
        log.info("写入spc信息完" )
        conn.commit()
        # c.close()
        # conn.close()
    except Exception as ex:
        log.error("处理 spc行 出错："+str(ex)+':'+sql)
        raise Exception("处理 spc行 出错："+str(ex)+':'+sql)
        
    scp.close()
    return file_info,c

def ngRows(cols,c):
    '''
    获取ng行
    '''
    # sql = "select * from spc where OPJudge='1'"
    sql = "select " + cols + " from spc,head where OPJudge='1'"
    try:
        c.execute(sql)
        rows = c.fetchall()
        return rows
    except Exception as ex:
        # 把ex 的错误信息存到一个字符串里
        return "查找ng出错" + str(ex)
    
def okRows(cols,c):
    '''
    获取ok行
    '''
    sql = "select " + cols + " from spc ,head where BlockNo not in (select distinct BlockNo from spc where OPJudge='1') group by BlockNo "
    try:
        c.execute(sql)
        rows = c.fetchall()
        return rows
    except Exception as ex:
        # 把ex 的错误信息存到一个字符串里
        return "查找ok出错" + str(ex)

def statistics(c):
    '''
    对各 BlockNo 中的各 InspectType 分类统计其中的  OPJudge为1 的个数 和不为1的个数
    '''
    sql = "select blockNo || '_' || InspectType as BlockNo_InspectType, count(*) as 'ng' from spc where OPJudge='-1' group by blockNo,InspectType"
    # c.execute(sql)
    # rows 有三列 blockNo,InspectType,ng 。以 blockNo 为key 建立一个字典，每个value是个小字典。 这小字典的key是 InspectType,value是ng
    rows = {}
    try:
        for row in c.execute(sql):
            if row[0] not in rows:
                rows[row[0]] = {}
            rows[row[0]][row[1]] = row[2] 
    except Exception as ex:
        log =  AppLogger('my_app.log', max_bytes=1024*5000, backup_count=2)
        log.error( "统计出错" + str(ex))
        return {}
    return rows


if __name__ == '__main__':
    file_info,c = parse_scp()
    r = ngRows('',c)
    print(r)